-- This script assumes that you have run up to 05_certsignserver.sql
SET NOCOUNT ON
USE PermTest
go

-- The base procedure. We have seen this version before, but the name is new.
CREATE PROCEDURE PermTest2_sp @a int, @b varchar(30) AS
   INSERT PermTable (a, b) VALUES (@a, @b)
   SELECT a, b, enteredby FROM PermTable
   PRINT 'The dynamic part'
   EXEC('SELECT a, reverse(b), enteredby FROM PermTable')
go
GRANT EXECUTE ON PermTest2_sp TO TestUser

-- As a repetition, this is what happens when TestUser runs the procedure.
EXECUTE AS USER = 'TestUser'
go
EXEC PermTest2_sp 13, 'Norwegian Wood'
go
REVERT

-- Create a proxy user for the procedure and grant permission on PermTable.
CREATE USER PermTest2_sp$Proxy WITHOUT LOGIN
GRANT SELECT ON PermTable TO PermTest2_sp$Proxy
go

-- Add EXECUTE AS clause to the procedure.
ALTER PROCEDURE PermTest2_sp @a int, @b varchar(30) 
  WITH EXECUTE AS 'PermTest2_sp$Proxy' AS
   INSERT PermTable (a, b) VALUES (@a, @b)
   SELECT a, b, enteredby FROM PermTable
   PRINT 'The dynamic part'
   EXEC('SELECT a, reverse(b), enteredby FROM PermTable')
go

-- Run as TestUser again. Pay attention to the enteredby column
EXECUTE AS USER = 'TestUser'
go
EXEC PermTest2_sp 14, 'Back in the USSR'
go
REVERT

-- Let's look more at how this works. Recreate the trigger.
CREATE TRIGGER PermTable_tri ON PermTable FOR INSERT AS
  SELECT 'PermTable_tri', original_login() AS original_login, 
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER,
          name AS token_name, type, usage 
  FROM    sys.user_token
go

-- Revisit outer_sp. First create a proxy user for it and grant permissions needed.
CREATE USER outer_sp$Proxy WITHOUT LOGIN
GRANT SELECT ON PermTable TO outer_sp$Proxy
go
ALTER PROCEDURE outer_sp @a int, @b varchar(30) 
 WITH EXECUTE AS 'outer_sp$Proxy' AS
   -- Return token information.
   SELECT 'outer_sp' AS SP, original_login() AS original_login, 
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER,
          name AS token_name, type, usage 
   FROM   sys.user_token

   -- Run some dynamic SQL.
   EXEC ('SELECT ''Dynamic SQL'' AS SP, name AS token_name, type, usage 
          FROM   sys.user_token
          SELECT a, b, enteredby FROM PermTable')

   -- Run a system procedure.
   EXEC sp_helpindex PermTable

   -- Run the inner procedure.
   EXEC inner_sp

   -- Add a row to the table and fire the trigger.
   INSERT PermTable(a, b) VALUES(@a, @b)

   -- Attempt to truncate the table.
   PRINT 'Attempt to truncate.'
   TRUNCATE TABLE PermTable
go

-- Run outer_sp as TestUser.
EXECUTE AS USER = 'TestUser'
go
EXEC outer_sp 15, 'With a Little Help from My Friends'
go
REVERT
go

-- What you have seen so far is the proper version of EXECUTE AS.
-- Here is the lazy version:
ALTER PROCEDURE outer_sp @a int, @b varchar(30) 
 WITH EXECUTE AS OWNER AS
   -- Return token information.
   SELECT 'outer_sp' AS SP, original_login() AS original_login, 
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER,
          name AS token_name, type, usage 
   FROM   sys.user_token

   -- Run some dynamic SQL.
   EXEC ('SELECT ''Dynamic SQL'' AS SP, name AS token_name, type, usage 
          FROM   sys.user_token
          SELECT a, b, enteredby FROM PermTable')

   -- Run a system procedure.
   EXEC sp_helpindex PermTable

   -- Run the inner procedure.
   EXEC inner_sp

   -- Add a row to the table and fire the trigger.
   INSERT PermTable(a, b) VALUES(@a, @b)

   -- Attempt to truncate the table.
   PRINT 'Attempt to truncate.'
   TRUNCATE TABLE PermTable
go


EXECUTE AS USER = 'TestUser'
go
EXEC outer_sp 16, 'Eight Days a Week'
go
REVERT

-- The table is now empty as TRUNCATE TABLE succeeded.
SELECT a, b, enteredby FROM PermTable

-- How to see which modules that have an EXECUTE AS clause:
SELECT object_name(object_id) AS Module, 
       CASE execute_as_principal_id WHEN -2 THEN 'OWNER'
            ELSE user_name(execute_as_principal_id)
       END AS [EXECUTE AS]
FROM   sys.sql_modules
WHERE  execute_as_principal_id IS NOT NULL
